﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace CarInfor.DAL
{
    /// <summary>
    /// 用户表
    /// </summary>
    public class UserInfor
    {
        #region 属性定义
        /// <summary>
        /// 主键标识
        /// </summary>
        string id = "";
        public string ID
        {
            get { return id; }
            set { id = value; }
        }

        /// <summary>
        /// 保险项名称
        /// </summary>
        private string userName = string.Empty;
        public string UserName
        {
            get { return userName; }
            set { userName = value; }
        }

        /// <summary>
        /// 密码
        /// </summary>
        private string password = string.Empty;
        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        #endregion

        #region 方法
        /// <summary>
        /// 无条件查询
        /// </summary>
        public DataTable Query()
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(""));
            }
            catch (Exception e)
            {
                throw (e);
            }
            return ds.Tables[0];
        }


        /// <summary>
        /// 有条件查询
        /// </summary>
        public DataTable Query(string condition)
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(condition));
            }
            catch (Exception e)
            {
                throw e;
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 根据主键查询
        /// </summary>
        public void QueryWithKey(string keyValue)
        {
            DataSet ds = null;
            try
            {
                string cond = "ID='" + keyValue + "'";
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(cond));
                if (ds.Tables[0].Rows.Count > 0)
                {
                    id = ds.Tables[0].Rows[0]["编号"].ToString();
                    userName = ds.Tables[0].Rows[0]["用户名"].ToString();
                    password = ds.Tables[0].Rows[0]["密码"].ToString();
                }
                else
                {
                    id = "";
                    userName = "";
                    password = "";
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        /// <summary>
        /// 获取查询Sql
        /// </summary>
        public string GetSqlString(string condition)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append("SELECT ");
                sql.Append(" ID as 编号,[UserName] as 用户名,[password] as 密码");
                sql.Append(" FROM [UserInfor]");
                if (!string.IsNullOrEmpty(condition))
                {
                    sql.Append(" WHERE " + condition + " order by [UserName] desc ");
                }
                else
                {
                    sql.Append(" order by [UserName] desc");
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
            return sql.ToString();

        }


        /// <summary>
        /// 通过主键更新
        /// </summary>
        public int UpdateWithKey(string keyValue)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append("UPDATE [UserInfor] SET ");
                sql.Append("[UserName]='" + format(userName) + "',");
                sql.Append("[Password]=" + password);
                sql.Append(" WHERE ID='" + keyValue + "'");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return db.ExecuteSql(sql.ToString());
        }


        /// <summary>
        ///添加一条记录
        /// </summary>
        public int Insert()
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append("INSERT into [UserInfor]");
                sql.Append("([ID],[UserName],[PassWord]) ");
                sql.Append(" VALUES(");
                sql.Append("'" + format(id) + "'");
                sql.Append(",'" + format(userName) + "'");
                sql.Append("," + password);
                sql.Append(")");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return db.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// 要求在调用IsDelete（）后使用
        /// 返回为true时，表示删除成功
        /// 返回为false时，表示删除失败
        /// </summary>
        public bool Delete(string keyValue)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append(" DELETE FROM [UserInfor] ");
                sql.Append(" WHERE ID='" + keyValue + "'");
                db.ExecuteSql(sql.ToString());
            }
            catch (Exception e)
            {
                throw (e);
            }
            return true;
        }

        /// <summary>
        ///根据条件删除
        /// </summary>
        public bool DeleteWithCondition(string condition)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append(" DELETE FROM [UserInfor] ");
                sql.Append(" WHERE " + condition);
                db.ExecuteSql(sql.ToString());
            }
            catch (Exception e)
            {
                throw (e);
            }
            return true;
        }
        /// <summary>
        /// 单引号的处理
        /// </summary>
        public string format(string myString)
        {
            return myString.Replace("'", "''");
        }

        #endregion
    }
}